%US banks and Inequality
%code for calculating asset distribution (for year = 1999)
%by Earth Tantasith 
%8 Mar 2021

%To understand code
%(1) simultaneous equation is solved within each bank
%(2) the aim is to construct matrix A and B for each bank

opts = spreadsheetImportOptions("NumVariables", 4);

% Specify sheet and range
opts.Sheet = "Sheet1";
opts.DataRange = "A1:D24082"; %24660 = no. of observation

% Specify column names and types
opts.VariableNames = ["cert_new", "fips", "depsumbr", "asset"];
opts.VariableTypes = ["double", "double", "double", "double"];

% Import the data
data = readtable("/Volumes/TOSHIBA EXT/FDIC data/1999/sd_call_1999_matlab.xlsx",opts, "UseExcel", false);

%% Convert to output type

data = table2array(data);

%% Clear temporary variables

clear opts;

for j= 1:10075 %9789 = no. of banks
  if j == 1 %code for j = 1 and the rest is the same. start with 1 to get initial "AsDis" matrix and append.
cert_new = data(:,1) == j; %choose the first column in which cert_new == j
v = data(cert_new,3);
s = data(cert_new,4);
s = s(1); %to get bank's total asset
v = v';
[m,n] = size(v);

A = zeros(n,n);

for i = 1:n
    if i == n
        A(i,:) = 1;
    else
    A(i,i) = 1/v(i);
    A(i,i+1) = -1/v(i+1);
    end
end

B = zeros(n,1);
B(n) = s;

X = linsolve(A,B);
AsDis = X;
check = sum(X);
  else
cert_new = data(:,1) == j;
v = data(cert_new,3);
s = data(cert_new,4);
s = s(1);
v = v';
[m,n] = size(v);

A = zeros(n,n);

for i = 1:n
    if i == n
        A(i,:) = 1;
    else
    A(i,i) = 1/v(i);
    A(i,i+1) = -1/v(i+1);
    end
end

B = zeros(n,1);
B(n) = s;
X = linsolve(A,B);
AsDis = [AsDis;X];
check = sum(X);
  end  
end
cert = data(:,1)
export = horzcat(cert,AsDis)
export = array2table(export)
writetable(export,'asset_dis_1999.xlsx')